package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Set;

import context.Context;
import exception.ViewException;

public class CreateView {

	public static void createViews(String tableName) throws ViewException {
		Context c = Context.newInstance();

		Statement st = null;


		try {

			Class.forName("com.mysql.jdbc.Driver");


			Connection con = DriverManager.getConnection(c.getConnection(),
					c.getUsrName(), c.getPsw());

			st = con.createStatement();
			//con.prepareStatement(tableName)
			LinkedHashMap<String, String> map = new LinkedHashMap<String,String>();

			String sql_recharge = "create view recharge as select cardID, " +
					"tradeDate, tradeTime, amount from "+ tableName +" where tradeType ='Z3004'";
			String sql_onlinepay = "create view onlinepay as select cardID, " +
					"tradeDate, tradeTime, amount from "+ tableName +" where tradeType ='00F00900' or tradeType = '01C00120'";
			String sql_pospay = "create view pospay as select cardID, tradeDate, " +
					"tradeTime, amount from "+ tableName +" where tradeType ='01C00121'";
			String sql_onlinerecharge = "create view onlinerecharge as select cardID, " +
					"tradeDate, tradeTime, amount from "+ tableName +" where tradeType ='01C00420'";
			String sql_swift = "create view swift as select cardID, tradeDate," +
					" tradeTime, amount from "+ tableName +" where tradeType ='01C02600'";
			String sql_totalrecharge = "create view totalrecharge as select cardID, tradeDate, " +
					"tradeTime, amount from "+ tableName +" where tradeType ='Z3004' or tradeType = '01C00420'";
			String sql_totalpay = "create view totalpay as select cardID, tradeDate, tradeTime," +
					" amount from "+ tableName +" where tradeType ='00F00900' or tradeType = '01C00120' " +
					"or tradeType = '01C00121' or tradeType = '01C02600'";
			String sql_merge_onlinepay = "create view merge_onlinepay as select tradeDate," +
					" sum(amount) as sum from onlinepay group by tradeDate";
			String sql_merge_onlinerecharge = "create view merge_onlinerecharge as select tradeDate," +
					" sum(amount) as sum from recharge group by tradeDate";
			String sql_merge_pospay = "create view merge_pospay as select tradeDate, " +
					"sum(amount) as sum from pospay group by tradeDate";
			String sql_merge_recharge = "create view merge_recharge as " +
					"select tradeDate, sum(amount) as sum from recharge group by tradeDate";
			String sql_merge_swift = "create view merge_swift as select tradeDate," +
					" sum(amount) as sum from swift group by tradeDate";
			String sql_merge_totalrecharge = "create view merge_totalrecharge as " +
					"select tradeDate, sum(amount) as sum from totalrecharge group by tradeDate";
			String sql_merge_totalpay = "create view merge_totalpay as " +
					"select tradeDate, sum(amount) as sum from totalpay group by tradeDate";
			String sql_merge_totalrecharge_id = "create view merge_totalrecharge_id as " +
					"select cardID, tradeDate, sum(amount) as sum from totalrecharge group by cardID,tradeDate";
			String sql_merge_totalpay_id = "create view merge_totalpay_id as " +
					"select cardID, tradeDate, sum(amount) as sum from totalpay group by cardID, tradeDate";
			map.put("recharge", sql_recharge);
			map.put("onlinepay", sql_onlinepay);
			map.put("pospay", sql_pospay);
			map.put("onlinerecharge", sql_onlinerecharge);
			map.put("swift", sql_swift);
			map.put("totalrecharge", sql_totalrecharge);
			map.put("totalpay", sql_totalpay);
			map.put("merge_onlinepay", sql_merge_onlinepay);
			map.put("merge_onlinerecharge", sql_merge_onlinerecharge);
			map.put("merge_pospay", sql_merge_pospay);
			map.put("merge_recharge", sql_merge_recharge);
			map.put("merge_swift", sql_merge_swift);
			map.put("merge_totalrecharge", sql_merge_totalrecharge);
			map.put("merge_totalpay", sql_merge_totalpay);
			map.put("merge_totalrecharge_id", sql_merge_totalrecharge_id);
			map.put("merge_totalpay_id", sql_merge_totalpay_id);
			
			Set<String> key = map.keySet();
			String[] keys = new String[key.size()];
			key.toArray(keys);
			
			//drop view if exists
			for( int i = 0; i < keys.length; i++) {
				String sql = "drop view if exists " + keys[i];
				st.execute(sql);
			}
			
			//create view
			for(int i = 0; i < keys.length; i++) {
				//System.out.println(map.get(keys[i]));
				st.execute(map.get(keys[i]));
			}
			


		} catch (Exception e) {

			e.printStackTrace();
			throw new ViewException("创建视图出错");
		}


	}
}
